--    Author    : ChenErHao
--    Name      : DM.T_MTH_LOAN_RATE.HQL
--    Functions : 利率-贷款利率分析
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-24  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.T_MTH_LOAN_RATE PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#')

SELECT
    BELONG_AREA_CODE,
    ORG_ID ORG_LVL_ID_2,
    CUSTOMER_TYPE,
    PRODUCT_CATEGORY LOAN_PRO_ID_1,--产品类别一级编码,
    LOANS_PERIOD LOANS_PERIOD,--贷款期限,
    LPIC LPIC_SUB_ID_1,--贷款主体行业一级编码,
    LOANS_ACTUALLY LOANS_ACTUALLY,--贷款实际投向,
    ENTERPRISE_SCALE ENTERPRISE_SCALE,--企业规模,
    ENTERPRISE_INVESTOR INVESTOR_FINICL_ID_2,--企业出资人经济成分二级编码,
    GUARANTY_STYLE LOAN_INSUR_ID_1,--贷款担保方式一级编码,
    LOAN_STATUS LOAN_STATUS,--贷款状态,
    LOAN_QUALITY LOAN_QUALITY,--贷款质量
    nvl(sum(CASE WHEN DATA_DATE='#V_DATA_DATE#' THEN  LOAN_BALANCE  END),0)    LOAN_BALANCE, --贷款余额
    nvl(sum(CASE WHEN DATA_DATE='#V_DATA_DATE#' THEN  LOAN_BALANCE_AMT   END),0)           LOAN_BALANCE_AMT, --贷款余额加权金额
    nvl(sum(CASE WHEN DATA_DATE='#V_LAST_1M_END_DATE#' THEN  LOAN_BALANCE     END),0)   LAST_MTH_LOAN_BALANCE, --上月贷款余额
    nvl(sum(CASE WHEN DATA_DATE='#V_LAST_1M_END_DATE#' THEN  LOAN_BALANCE_AMT  END),0)  LAST_MTH_LOAN_BALANCE_AMT, --上月贷款余额加权金额
    nvl(sum(CASE WHEN DATA_DATE='#V_LAST_1M_END_DATE#' THEN  LOAN_BALANCE_RATE_SUM     END),0)   LOAN_BALANCE_RATE_SUM_L, --上月贷款余额利率合计
    nvl(sum(CASE WHEN DATA_DATE='#V_LAST_1M_END_DATE#' THEN  LOAN_BALANCE_STROKE_COUNT   END),0) LOAN_BALANCE_STROKE_COUNT_L, --上月贷款余额笔数
    nvl(sum(CASE WHEN DATA_DATE='#V_DATA_DATE#' THEN  LOAN_BALANCE_RATE_SUM    END),0)  LOAN_BALANCE_RATE_SUM, --贷款余额利率合计
    nvl(sum(CASE WHEN DATA_DATE='#V_DATA_DATE#' THEN  LOAN_BALANCE_STROKE_COUNT  END),0) LOAN_BALANCE_STROKE_COUNT, --贷款余额笔数
    nvl(sum(CASE WHEN DATA_DATE='#V_DATA_DATE#' THEN  LOAN_AMOUNT                END),0)  LOAN_AMOUNT, --贷款发生额
    nvl(sum(CASE WHEN DATA_DATE='#V_DATA_DATE#' THEN  LOAN_AMOUNT_AMT          END),0)    LOAN_AMOUNT_AMT, --款发生额加权金额
    nvl(sum(CASE WHEN DATA_DATE='#V_LAST_1M_END_DATE#' THEN  LOAN_AMOUNT      END),0)   LAST_MTH_LOAN_AMOUNT, --上月贷款发生额
    nvl(sum(CASE WHEN DATA_DATE='#V_LAST_1M_END_DATE#' THEN  LOAN_AMOUNT_AMT   END),0)  LAST_MTH_LOAN_AMOUNT_AMT, --上月贷款发生额加权金额
    nvl(sum(CASE WHEN DATA_DATE='#V_DATA_DATE#' THEN  LOAN_AMOUNT_RATE_SUM      END),0)   LOAN_AMOUNT_RATE_SUM, --贷款发生额利率合计
    nvl(sum(CASE WHEN DATA_DATE='#V_DATA_DATE#' THEN  LOAN_AMOUNT_STROKE_COUNT    END),0) LOAN_AMOUNT_STROKE_COUNT, --贷款发生额笔数
    nvl(sum(CASE WHEN DATA_DATE='#V_LAST_1M_END_DATE#' THEN  LOAN_AMOUNT_RATE_SUM      END),0)  LOAN_AMOUNT_RATE_SUM_L, --上月贷款发生额利率合计
    nvl(sum(CASE WHEN DATA_DATE='#V_LAST_1M_END_DATE#' THEN  LOAN_AMOUNT_STROKE_COUNT  END),0)  LOAN_AMOUNT_STROKE_COUNT_L --上月贷款发生额笔数
FROM
    (SELECT A.DATA_DATE,
            BELONG_AREA_CODE,
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END ORG_ID,
            CUSTOMER_TYPE,
            CASE WHEN C.loan_pro_id_1 IS NULL THEN '9999'
                 WHEN C.loan_pro_id_1 ='' THEN '9999'
                 ELSE C.loan_pro_id_1 END PRODUCT_CATEGORY,--贷款产品类别
            LOANS_PERIOD,--贷款期限,
            NVL(LPIC_SUB,'9999') LPIC,--企业分行业
            CASE WHEN G.loan_actually_id_1 IS NULL THEN '9999'
                 WHEN G.loan_actually_id_1 ='' THEN '9999'
                 ELSE G.loan_actually_id_1 END LOANS_ACTUALLY,--行业投向
            nvl(ENTERPRISE_SCALE,'CS05') ENTERPRISE_SCALE,---企业规模,
            CASE WHEN E.investor_finicl_id_2 IS NULL THEN '9999'
                 WHEN E.investor_finicl_id_2 ='' THEN '9999'
                 ELSE E.investor_finicl_id_2 END  ENTERPRISE_INVESTOR,--企业出资人经济成分二级编码
            CASE WHEN F.loan_insur_id_1 IS NULL THEN '9999'
                 WHEN F.loan_insur_id_1 ='' THEN '9999'
                 ELSE F.loan_insur_id_1 END   GUARANTY_STYLE,--?畹１７绞揭患侗嗦?
            nvl(LOAN_STATUS,'9999') LOAN_STATUS,--贷款状态,
            nvl(LOAN_QUALITY,'9999') LOAN_QUALITY,--贷款质量
            SUM(LOAN_BALANCE)      LOAN_BALANCE,
            SUM(LOAN_OCCURRENCE_LEVEL) LOAN_BALANCE_AMT,
            0                      LAST_MTH_LOAN_BALANCE,
            0                      LAST_MTH_LOAN_BALANCE_AMT,
            SUM(RATE_LEVEL)        LOAN_BALANCE_RATE_SUM,
            SUM(STROKE_COUNT)      LOAN_BALANCE_STROKE_COUNT,
            0                      LOAN_BALANCE_RATE_SUM_L,
            0                      LOAN_BALANCE_STROKE_COUNT_L,
            0                      LOAN_AMOUNT,
            0                      LOAN_AMOUNT_AMT,
            0                      LAST_MTH_LOAN_AMOUNT,
            0                      LAST_MTH_LOAN_AMOUNT_AMT,
            0                      LOAN_AMOUNT_RATE_SUM,
            0                      LOAN_AMOUNT_STROKE_COUNT,
            0                      LOAN_AMOUNT_RATE_SUM_L,
            0                      LOAN_AMOUNT_STROKE_COUNT_L
        FROM
             (SELECT DATA_DATE,CUSTOMER_TYPE,LOANS_PERIOD,ENTERPRISE_SCALE,LOAN_STATUS,LOAN_QUALITY,BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY,LPIC_SUB,LOANS_ACTUALLY,ENTERPRISE_INVESTOR,GUARANTY_STYLE,
           LOAN_BALANCE,LOAN_OCCURRENCE_LEVEL,RATE_LEVEL,STROKE_COUNT
           FROM EDW.LOAB
        WHERE 
            DATA_DATE in ('#V_DATA_DATE#' ,'#V_LAST_1M_END_DATE#')) A
         LEFT JOIN DMCODE.T_ORG_BIZ_LVL B ON CASE WHEN a.DATA_ORG_CODE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.DATA_ORG_CODE end= B.ORG_ID  --金融机构
         LEFT JOIN DMCODE.T_LOAN_PRO_TYPE C ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.PRODUCT_CATEGORY end= C.LOAN_PRO_ID --贷款产品类别
         LEFT JOIN DMCODE.T_LOANS_ACTUALLY G ON CASE WHEN a.LOANS_ACTUALLY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.LOANS_ACTUALLY end=G.LOAN_ACTUALLY_ID   --贷款行业投向
         LEFT JOIN DMCODE.T_INVESTOR_FINICL_TYPE_TRANS E ON CASE WHEN a.ENTERPRISE_INVESTOR IS NULL THEN CONCAT('org_code_hive',RAND()) else A.ENTERPRISE_INVESTOR end= E.INVESTOR_FINICL_ID --企业出资人经济成分
         LEFT JOIN DMCODE.T_LOAN_INSUR_TYPE F ON CASE WHEN a.GUARANTY_STYLE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.GUARANTY_STYLE end=F.LOAN_INSUR_ID   --贷款担保方式 
        GROUP BY A.DATA_DATE ,A.BELONG_AREA_CODE  , B.ORG_LVL_ID_2  ,
            CUSTOMER_TYPE, C.LOAN_PRO_ID_1  ,LOANS_PERIOD,--贷款期限, 
            LPIC_SUB  , G.loan_actually_id_1  ,ENTERPRISE_SCALE,--企业规模, 
            E.INVESTOR_FINICL_ID_2  , F.LOAN_INSUR_ID_1  ,
            LOAN_STATUS,
            LOAN_QUALITY  
            UNION ALL 
  SELECT A.DATA_DATE ,
            BELONG_AREA_CODE ,
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END ORG_ID,
            CUSTOMER_TYPE,
            CASE WHEN C.loan_pro_id_1 IS NULL THEN '9999'
                 WHEN C.loan_pro_id_1 ='' THEN '9999'
                 ELSE C.loan_pro_id_1 END PRODUCT_CATEGORY,--贷款产品类别
            LOANS_PERIOD,--贷款期限,
            NVL(LPIC_SUB,'9999') LPIC,--企业分行业
            CASE WHEN G.loan_actually_id_1 IS NULL THEN '9999'
                 WHEN G.loan_actually_id_1 ='' THEN '9999'
                 ELSE G.loan_actually_id_1 END LOANS_ACTUALLY,--行业投向
            nvl(ENTERPRISE_SCALE,'CS05') ENTERPRISE_SCALE,--企业规模,
            CASE WHEN E.investor_finicl_id_2 IS NULL THEN '9999'
                 WHEN E.investor_finicl_id_2 ='' THEN '9999'
                 ELSE E.investor_finicl_id_2 END  ENTERPRISE_INVESTOR,--企业出资人经济成分二级编码
            CASE WHEN F.loan_insur_id_1 IS NULL THEN '9999'
                 WHEN F.loan_insur_id_1 ='' THEN '9999'
                 ELSE F.loan_insur_id_1 END   GUARANTY_STYLE,--贷款担保方式一级编码
            nvl(LOAN_STATUS,'9999') LOAN_STATUS,--贷款状态,
            '' LOAN_QUALITY,
            0,
            0,
            0,
            0,
            0,
            0,
            0 ,
            0 ,
            SUM(LOAN_OCCURRENCE)   LOAN_AMOUNT,
            SUM(LOAN_OCCURRENCE_LEVEL) LOAN_AMOUNT_AMT,
            0                      LAST_MTH_LOAN_AMOUNT,
            0                      LAST_MTH_LOAN_AMOUNT_AMT,
            SUM(RATE_LEVEL)        LOAN_AMOUNT_RATE_SUM,
            SUM(STROKE_COUNT)      LOAN_AMOUNT_STROKE_COUNT,
            0                      LOAN_AMOUNT_RATE_SUM_L,
            0                      LOAN_AMOUNT_STROKE_COUNT_L
     FROM
         (SELECT BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY,LPIC_SUB, LOANS_ACTUALLY, ENTERPRISE_INVESTOR,
        GUARANTY_STYLE, DATA_DATE,CUSTOMER_TYPE,LOANS_PERIOD,ENTERPRISE_SCALE,LOAN_STATUS,LOAN_OCCURRENCE,LOAN_OCCURRENCE_LEVEL,RATE_LEVEL,STROKE_COUNT
        FROM EDW.LOAF
    WHERE
           DATA_DATE in ('#V_DATA_DATE#', '#V_LAST_1M_END_DATE#') 
           ) A
           LEFT JOIN DMCODE.T_ORG_BIZ_LVL B ON A.DATA_ORG_CODE = B.ORG_ID  --金融机构
           LEFT JOIN DMCODE.t_loan_pro_type C ON A.PRODUCT_CATEGORY = C.LOAN_PRO_ID --贷款产品类别
           LEFT JOIN DMCODE.t_loans_actually G ON A.LOANS_ACTUALLY=G.loan_actually_id   --贷款行业投向
           LEFT JOIN DMCODE.t_investor_finicl_type_trans E ON A.ENTERPRISE_INVESTOR = E.investor_finicl_id --企业出资人经济成分
           LEFT JOIN DMCODE.t_loan_insur_type F ON A.GUARANTY_STYLE=F.loan_insur_id   --贷款担保方式
    GROUP BY A.DATA_DATE , A.BELONG_AREA_CODE   , B.ORG_LVL_ID_2  ,
            CUSTOMER_TYPE, C.LOAN_PRO_ID_1  ,
            LOANS_PERIOD,--贷款期限, 
            LPIC_SUB  , G.loan_actually_id_1  ,
            ENTERPRISE_SCALE,--企业规模,
            E.INVESTOR_FINICL_ID_2  , F.LOAN_INSUR_ID_1  ,
            LOAN_STATUS ) T
GROUP BY
    BELONG_AREA_CODE,
    ORG_ID,
    CUSTOMER_TYPE,
    PRODUCT_CATEGORY,
    LOANS_PERIOD ,
    LPIC ,
    LOANS_ACTUALLY,
    ENTERPRISE_SCALE,
    ENTERPRISE_INVESTOR,
    GUARANTY_STYLE,
    LOAN_STATUS ,
    LOAN_QUALITY ;